package com.atguigu.gmall.gmallsugar.mapper;

import com.atguigu.gmall.gmallsugar.bean.Province;
import com.atguigu.gmall.gmallsugar.bean.Tm;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface TradeMapper {
    
    // 查询指定日期的总的销售额
    @Select("SELECT sum(order_amount) AS order_amount\n" +
        "FROM dws_trade_sku_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date} ")
    BigDecimal gmv(Integer date);
    
    @Select("SELECT\n" +
        "    spu_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM dws_trade_sku_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY spu_name\n" +
        "ORDER BY order_amount DESC\n" +
        "LIMIT #{limit}")
    List<Map<String, Object>> gmvBySpu(Integer date, Integer limit);
    
    @Select("SELECT\n" +
        "    trademark_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM dws_trade_sku_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY trademark_name")
    List<Tm> gmvByTm(Integer date);
    
    @Select("SELECT\n" +
        "    province_name,\n" +
        "    sum(order_amount) AS order_amount,\n" +
        "    sum(order_count)AS order_count\n" +
        "FROM dws_trade_province_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY province_name")
    List<Province> province(Integer date);
    
}
